import os
import xlrd
import xlwt

# 获取当前目录下的所有.xls文件
files = [f for f in os.listdir('.') if os.path.isfile(f) and f.endswith('.xls')]
paths = "./转换之后/"
# 检查路径是否存在，如果不存在则创建
if not os.path.exists(paths):
    os.makedirs(paths)
# 创建一个列表来跟踪已经处理过的文件
processed_files = []

for file_path in files:
    # 检查文件是否已经被处理过
    if file_path in processed_files:
        continue  # 如果文件已经被处理过，跳过该文件

    # 打开Excel文件
    workbook = xlrd.open_workbook(file_path)

    # 获取最后一个工作表
    last_sheet = workbook.sheet_by_index(workbook.nsheets - 1)

    # 读取工作表中的数据
    data = []
    purchase_order_number = None

    for row in range(last_sheet.nrows):
        row_values = last_sheet.row_values(row)
        if "采购订单:" in row_values:
            purchase_order_number = row_values[row_values.index("采购订单:") + 1]
            break

    start_row = row + 2  # 跳过标题行和空行

    for row in range(start_row, last_sheet.nrows):
        row_values = last_sheet.row_values(row)
        data.append([
            row_values[2],  # 零件号
            row_values[1],  # 零件名称
            row_values[3],  # 到货数量
            row_values[4]   # 计量单位
        ])

    # 创建一个Excel工作簿
    new_workbook = xlwt.Workbook()

    # 添加一个工作表
    new_worksheet = new_workbook.add_sheet('供应商供货清单')

    # 设置表头和其他信息
    info = [
        ('发运编号：', '', '', '', '', '', '客户：', '长春一汽富维高新汽车饰件有限公司', '', '', ''),
        ('供应商', '', '', '', '', '', '采购订单编号：', '', '', '', ''),
        ('供应商地址：', '', '', '', '', '', '收货地址：', '', '', '', ''),
        ('序号', '零件号', '零件名称', '到货数量', '单位', '使用车型', '到货包装数', '标准包装数', '批次', '送货通知单号', '备注')
    ]
    font_style = xlwt.easyxf('font: height 280, bold on;')  # 设置字体大小为14，加粗
    borders = xlwt.Borders()  # 创建边框对象
    borders.left = xlwt.Borders.THIN
    borders.right = xlwt.Borders.THIN
    borders.top = xlwt.Borders.THIN
    borders.bottom = xlwt.Borders.THIN
    font_style.borders = borders  # 将边框应用到字体样式
    # 写入表头和其他信息
    for row, row_data in enumerate(info):
        for col, cell_data in enumerate(row_data):
            new_worksheet.write(row + 1, col, cell_data)

    for col in range(11):  # 设置所有列的宽度为15个字符宽度
        new_worksheet.col(col).width = 256 * 23

    title_style = xlwt.easyxf('align: horiz center, vert center; font: height 280, bold on;')
    title_style.borders = borders
    new_worksheet.write_merge(0, 0, 0, 10, '供应商供货清单（国内供应商）', title_style)

    data_style = xlwt.easyxf('font: height 280;')
    data_style.borders = borders
    # 写入提取到的数据
    for index, row_data in enumerate(data):
        new_worksheet.write(index + 5, 0, index + 1)
        new_worksheet.write(index + 5, 1, row_data[0])
        new_worksheet.write(index + 5, 2, row_data[1])
        new_worksheet.write(index + 5, 3, row_data[2])
        new_worksheet.write(index + 5, 4, row_data[3])
        new_worksheet.write(index + 5, 9, purchase_order_number)

    new_file_path = os.path.join(paths, file_path)
    new_workbook.save(new_file_path)
